1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmQuotationRecord
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select Q_ID, RTRIM(QuotationNo), Date, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ContactNo),GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation where Customer.ID=quotation.CustomerID order by Date", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillQuotationNo()
25 End Sub
26
27 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
31 frmQuotation.Show()
32 Me.Hide()
33 frmQuotation.txtID.Text = dr.Cells(0).Value.ToString()
34 frmQuotation.txtQuotationNo.Text = dr.Cells(1).Value.ToString()
35 frmQuotation.dtpQuotationDate.Text = dr.Cells(2).Value.ToString()
36 frmQuotation.txtCustomerID.Text = dr.Cells(4).Value.ToString()
37 frmQuotation.txtCID.Text = dr.Cells(3).Value.ToString()
38 frmQuotation.txtCustomerID.Text = dr.Cells(4).Value.ToString()
39 frmQuotation.txtCustomerName.Text = dr.Cells(5).Value.ToString()
40 frmQuotation.txtContactNo.Text = dr.Cells(6).Value.ToString()
41 frmQuotation.txtGrandTotal.Text = dr.Cells(7).Value.ToString()
42 frmQuotation.txtRemarks.Text = dr.Cells(8).Value.ToString()
43 frmQuotation.btnSave.Enabled = False
44 frmQuotation.btnUpdate.Enabled = True
45 frmQuotation.btnPrint.Enabled = True
46 frmQuotation.btnDelete.Enabled = True
47 frmQuotation.lblSet.Text = "Not Allowed"
48 frmQuotation.btnAdd.Enabled = False
49 con = New SqlConnection(cs)
50 con.Open()
51 Dim sql As String = "SELECT RTRIM(ProductCode),RTRIM(ProductName), Quotation_Join.Cost, Quotation_Join.Qty, Quotation_Join.Amount, Quotation_Join.DiscountPer, Quotation_Join.Discount, Quotation_Join.VATPer, Quotation_Join.VAT, Quotation_Join.TotalAmount,Product.PID from quotation,Quotation_Join,Product where quotation.Q_ID=Quotation_Join.QuotationID and Product.PID=Quotation_Join.ProductID and quotation.Q_ID=@d1"
52 cmd = New SqlCommand(sql, con)
53 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
54 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
55 frmQuotation.DataGridView1.Rows.Clear()
56 While (rdr.Read() = True)
57 frmQuotation.DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
58 End While
59 con.Close()
60 con = New SqlConnection(cs)
61 con.Open()
62 Dim ct As String = "select RTRIM(CustomerType) from Customer where ID=" & dr.Cells(3).Value & ""
63 cmd = New SqlCommand(ct)
64 cmd.Connection = con
65 rdr = cmd.ExecuteReader()
66 If rdr.Read Then
67 frmQuotation.txtCustomerType.Text = rdr.GetValue(0)
68 If Not rdr Is Nothing Then
69 rdr.Close()
70 End If
71 Exit Sub
72 End If
73 con.Close()
74 End If
75 Catch ex As Exception
76 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
77 End Try
78 End Sub
79
80 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
81 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
82 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
83 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
84 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
85 End If
86 Dim b As Brush = SystemBrushes.ControlText
87 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
88
89 End Sub
90 Sub fillQuotationNo()
91 Try
92 con = New SqlConnection(cs)
93 con.Open()
94 adp = New SqlDataAdapter()
95 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(QuotationNo) FROM quotation", con)
96 ds = New DataSet("ds")
97 adp.Fill(ds)
98 dtable = ds.Tables(0)
99 cmbQuotationNo.Items.Clear()
100 For Each drow As DataRow In dtable.Rows
101 cmbQuotationNo.Items.Add(drow(0).ToString())
102 Next
103 Catch ex As Exception
104 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
105 End Try
106 End Sub
107 Sub Reset()
108 cmbQuotationNo.Text = ""
109 txtCustomerName.Text = ""
110 fillQuotationNo()
111 dtpDateFrom.Text = Today
112 dtpDateTo.Text = Today
113 Getdata()
114 End Sub
115 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
116 Reset()
117 End Sub
118
119 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
120 Me.Close()
121 End Sub
122
123
124 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
125 Dim rowsTotal, colsTotal As Short
126 Dim I, j, iC As Short
127 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
128 Dim xlApp As New Excel.Application
129 Try
130 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
131 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
132 xlApp.Visible = True
133
134 rowsTotal = dgw.RowCount
135 colsTotal = dgw.Columns.Count - 1
136 With excelWorksheet
137 .Cells.Select()
138 .Cells.Delete()
139 For iC = 0 To colsTotal
140 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
141 Next
142 For I = 0 To rowsTotal - 1
143 For j = 0 To colsTotal
144 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
145 Next j
146 Next I
147 .Rows("1:1").Font.FontStyle = "Bold"
148 .Rows("1:1").Font.Size = 12
149
150 .Cells.Columns.AutoFit()
151 .Cells.Select()
152 .Cells.EntireColumn.AutoFit()
153 .Cells(1, 1).Select()
154 End With
155 Catch ex As Exception
156 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
157 Finally
158 'RELEASE ALLOACTED RESOURCES
159 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
160 xlApp = Nothing
161 End Try
162 End Sub
163
164 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
165 Try
166 con = New SqlConnection(cs)
167 con.Open()
168 cmd = New SqlCommand("Select Q_ID, RTRIM(QuotationNo), Date, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ContactNo),GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation where Customer.ID=quotation.CustomerID and Date between @d1 and @d2 order by Date", con)
169 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
170 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
171 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
172 dgw.Rows.Clear()
173 While (rdr.Read() = True)
174 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
175 End While
176 con.Close()
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181
182 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbQuotationNo.SelectedIndexChanged
183 Try
184 con = New SqlConnection(cs)
185 con.Open()
186 cmd = New SqlCommand("Select Q_ID, RTRIM(QuotationNo), Date, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ContactNo),GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation where Customer.ID=quotation.CustomerID and QuotationNo='" & cmbQuotationNo.Text & "' order by Date", con)
187 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
188 dgw.Rows.Clear()
189 While (rdr.Read() = True)
190 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
191 End While
192 con.Close()
193 Catch ex As Exception
194 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
195 End Try
196 End Sub
197
198 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
199 Try
200 con = New SqlConnection(cs)
201 con.Open()
202 cmd = New SqlCommand("Select Q_ID, RTRIM(QuotationNo), Date, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ContactNo),GrandTotal, RTRIM(quotation.Remarks) from Customer,quotation where Customer.ID=quotation.CustomerID and Name like '%" & txtCustomerName.Text & "%' order by Date", con)
203 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
204 dgw.Rows.Clear()
205 While (rdr.Read() = True)
206 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
207 End While
208 con.Close()
209 Catch ex As Exception
210 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
211 End Try
212 End Sub
213
214 Private Sub cmbQuotationNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbQuotationNo.Format
215 If (e.DesiredType Is GetType(String)) Then
216 e.Value = e.Value.ToString.Trim
217 End If
218 End Sub
219 End Class
